"""
数据库连接测试工具

用于验证数据库连接并查看需要清理的重复seq_no数据。
这是一个安全的只读脚本，不会修改任何数据。
"""

import mysql.connector
from mysql.connector import Error
import os
import importlib.util
import sys
from tabulate import tabulate

def load_config(config_path='config.py'):
    """加载配置文件"""
    try:
        if os.path.exists(config_path):
            spec = importlib.util.spec_from_file_location("config", config_path)
            config = importlib.util.module_from_spec(spec)
            spec.loader.exec_module(config)
            return config
        else:
            print(f"配置文件 {config_path} 不存在，将使用默认配置")
            return None
    except Exception as e:
        print(f"加载配置文件时出错: {e}")
        return None

# 默认配置
DEFAULT_DB_CONFIG = {
    'host': '10.10.10.164',
    'port': 33066,
    'database': 'trade-service-platform',
    'user': 'root',
    'password': '2020jieguan2023'
}

def connect_to_database(db_config):
    """连接到MySQL数据库"""
    try:
        connection = mysql.connector.connect(**db_config)
        if connection.is_connected():
            print(f"✅ 已成功连接到MySQL数据库: {db_config['database']}")
            return connection
    except Error as e:
        print(f"❌ 连接数据库时发生错误: {e}")
        return None

def check_tables_existence(connection):
    """检查必要的表是否存在"""
    required_tables = ['nems_invt_head', 'nems_invt_list', 'storage_detail']
    cursor = connection.cursor()
    
    try:
        cursor.execute("SHOW TABLES")
        existing_tables = [table[0] for table in cursor.fetchall()]
        
        print("\n表存在性检查:")
        for table in required_tables:
            if table in existing_tables:
                print(f"✅ 表 {table} 存在")
            else:
                print(f"❌ 表 {table} 不存在")
    except Error as e:
        print(f"❌ 检查表时发生错误: {e}")
    finally:
        cursor.close()

def get_duplicate_seq_no(connection, tenant_id=None, limit=10):
    """获取重复的seq_no数据"""
    cursor = connection.cursor(dictionary=True)
    
    try:
        # 查询重复的seq_no
        query = """
        SELECT seq_no, COUNT(*) as count
        FROM nems_invt_head
        WHERE seq_no IS NOT NULL AND seq_no != ''
        """
        
        params = []
        if tenant_id:
            query += " AND TENANT_ID = %s"
            params.append(tenant_id)
        
        query += """
        GROUP BY seq_no
        HAVING COUNT(*) > 1
        LIMIT %s
        """
        params.append(limit)
        
        cursor.execute(query, params)
        duplicates = cursor.fetchall()
        
        print(f"\n找到 {len(duplicates)} 个重复的seq_no (限制 {limit} 条):")
        
        if duplicates:
            # 使用tabulate显示结果
            headers = duplicates[0].keys()
            rows = [d.values() for d in duplicates]
            print(tabulate(rows, headers=headers, tablefmt="grid"))
            
            # 获取第一个重复记录的详细信息
            if duplicates:
                get_duplicate_details(connection, duplicates[0]['seq_no'])
        else:
            print("没有找到重复的seq_no记录。")
            
        return duplicates
    except Error as e:
        print(f"❌ 查询重复seq_no时发生错误: {e}")
        return []
    finally:
        cursor.close()

def get_duplicate_details(connection, seq_no):
    """获取特定seq_no的重复记录详细信息"""
    cursor = connection.cursor(dictionary=True)
    
    try:
        # 查询该seq_no的所有记录
        query = """
        SELECT h.id as head_id, h.seq_no, 
               COUNT(l.id) as list_count, 
               (SELECT COUNT(*) FROM storage_detail s 
                JOIN nems_invt_list l2 ON s.INVT_LIST_ID = l2.id 
                WHERE l2.inv_id = h.id) as storage_count
        FROM nems_invt_head h
        LEFT JOIN nems_invt_list l ON l.inv_id = h.id
        WHERE h.seq_no = %s
        GROUP BY h.id, h.seq_no
        """
        
        cursor.execute(query, (seq_no,))
        records = cursor.fetchall()
        
        print(f"\n序列号 {seq_no} 的详细信息:")
        if records:
            # 使用tabulate显示结果
            headers = records[0].keys()
            rows = [r.values() for r in records]
            print(tabulate(rows, headers=headers, tablefmt="grid"))
            
            # 解释一下哪些记录会被保留，哪些会被删除
            print("\n根据清理规则:")
            for record in records:
                if record['storage_count'] > 0:
                    print(f"✅ 表头ID: {record['head_id']} 将被保留 (有关联的入库单)")
                else:
                    print(f"❌ 表头ID: {record['head_id']} 将被删除 (没有关联的入库单)")
        else:
            print(f"未找到序列号为 {seq_no} 的记录。")
    except Error as e:
        print(f"❌ 查询详细信息时发生错误: {e}")
    finally:
        cursor.close()

def main():
    """主函数"""
    print("========================")
    print("数据库连接测试工具")
    print("========================\n")
    
    # 使用默认配置文件路径或命令行参数指定的路径
    config_path = 'config.py'
    if len(sys.argv) > 1:
        config_path = sys.argv[1]
    
    # 加载配置
    config = load_config(config_path)
    db_config = getattr(config, 'DB_CONFIG', DEFAULT_DB_CONFIG) if config else DEFAULT_DB_CONFIG
    cleanup_config = getattr(config, 'CLEANUP_CONFIG', {}) if config else {}
    
    # 连接数据库
    connection = connect_to_database(db_config)
    if not connection:
        print("程序退出")
        return
    
    try:
        # 检查表是否存在
        check_tables_existence(connection)
        
        # 获取租户ID
        tenant_id = None
        if cleanup_config.get('include_tenant_id'):
            tenant_ids = cleanup_config.get('include_tenant_id')
            if tenant_ids and len(tenant_ids) > 0:
                tenant_id = tenant_ids[0]
                print(f"\n使用配置的租户ID: {tenant_id}")
        
        # 获取重复的seq_no
        get_duplicate_seq_no(connection, tenant_id)
        
    except Exception as e:
        print(f"❌ 执行过程中发生错误: {e}")
    finally:
        if connection.is_connected():
            connection.close()
            print("\n数据库连接已关闭")
            print("========================")

if __name__ == "__main__":
    main() 